package app.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import net.sf.json.JSONArray;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;

public class UtilDbOracle {
	private final String DRIVER = "oracle.jdbc.driver.OracleDriver";// Oracle的驱动
	private final static String URL = "jdbc:oracle:thin:@192.168.128.129:1521:orcl";// Oracle9i为Oracle的主机串名（也就是Oracle的SID）在装Oracle的时候填写的SID
	private final static String DBUSER = "TTT";// 这里就以scott/tiger为用户登录
	private final static String DBPASSWORD = "TTT";
	private static Connection con = null;// 预先定义一个连接接口

	public UtilDbOracle() {// 在构造方法中加载数据库驱动
		try {
			Class.forName(DRIVER).newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() { // 连接数据库的方法
		try {
			con = DriverManager.getConnection(URL, DBUSER, DBPASSWORD);
		} catch (Exception ew) {
			ew.printStackTrace();
		}

		return con;
	}

	public static JSONArray list(String sql) {
		JSONArray result=new JSONArray();
		try {
			result = convertResultSetToJsonArr(getConnection().createStatement().executeQuery(sql));
		} catch (JSONException | SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static void main(String[] args) {
		System.out.println(list("SELECT * from TTT"));
		/*
		try {
			UtilDbOracle oracleJdbcTest = new UtilDbOracle();
			Connection con = oracleJdbcTest.getConnection();
			ResultSet rs = con.createStatement().executeQuery("SELECT * from TTT");
			while (rs.next()) {
				System.out.println(rs.getString("id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		*/
	}

	public static JSONArray convertResultSetToJsonArr(ResultSet rs) throws SQLException, JSONException {
		JSONArray json = new JSONArray();
		ResultSetMetaData rsmd = rs.getMetaData();

		while (rs.next()) {
			int numColumns = rsmd.getColumnCount();
			JSONObject obj = new JSONObject();

			for (int i = 1; i < numColumns + 1; i++) {
				String column_name = rsmd.getColumnName(i);

				if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
					obj.put(column_name, rs.getArray(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
					obj.put(column_name, rs.getBoolean(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
					obj.put(column_name, rs.getBlob(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
					obj.put(column_name, rs.getDouble(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
					obj.put(column_name, rs.getFloat(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
					obj.put(column_name, rs.getNString(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
					obj.put(column_name, rs.getString(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
					obj.put(column_name, rs.getDate(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
					obj.put(column_name, rs.getTimestamp(column_name));
				} else {
					obj.put(column_name, rs.getObject(column_name));
				}
			}

			json.add(obj);
		}

		return json;
	}
}
